from sqlalchemy import text

from py.PojosAndDaos.Account import Account_class
from py.PojosAndDaos.Book import Book_class
from py.PojosAndDaos import Record


class BookService:

    # 对书名的模糊查找
    @classmethod
    def Book_Query_All(cls):
        session = Book_class().session
        bookList = session.query(Book_class()).all()
        session.close()
        return bookList

    @classmethod
    def Book_Query_ByName(cls, name):
        session = Book_class().session
        bookList = session.query(Book_class()).filter(Book_class().name.like('%' + name + '%'))
        session.close()
        return bookList

    # 对类别的模糊查找

    @classmethod
    def Book_Query_ByCategory(cls, category):
        session = Book_class().session
        bookList = session.query(Book_class()).filter(Book_class().category.like('%' + category + '%'))
        session.close()
        return bookList

    # 对国际标准数码的精确查找
    @classmethod
    def Book_Query_ByISBN(cls, ISBN):
        session = Book_class().session
        book = session.query(Book_class()).filter_by(ISBN=ISBN).one_or_none()
        if book is None:
            return "通过ISBN没查到这本书"
        session.close()
        return book

    # 书目更新
    @classmethod
    def Book_Update(cls, book: Book_class()):
        bookTem=BookService.Book_Query_ByISBN(book.ISBN)
        if bookTem=="通过ISBN没查到这本书":
            return "这本书不在系统中，无法修改"
        session = Book_class().session
        session.execute(text(
            "UPDATE BOOKTABLE "
            "SET BOOKTABLE.NAME = '{}', PRESS = '{}', EDITOR = '{}', CATEGORY= '{}', ATTR1 = '{}', "
            "ATTR2 = '{}', ATTR3 = '{}', ATTR4 = '{}', AMOUNT = '{}', BOOKTABLE.LEFT = '{}', PRICE = '{}' "
            .format(book.name, book.press, book.editor, book.category, book.attr1, book.attr2, book.attr3, book.attr4,
                    book.amount, book.left, book.price) +
            "WHERE ISBN = " + book.ISBN
        ))
        session.commit()
        session.close()
        return "书目信息更新成功！"

    # 书目删除
    @classmethod
    def Book_Delete(cls, book: Book_class()):
        session = Book_class().session
        session.query(Book_class()).filter_by(ISBN=book.ISBN).delete()
        session.commit()
        session.close()
        return "书目删除成功！"

    # 书目添加
    @classmethod
    def Book_Insert(cls, book: Book_class()):
        session = Book_class().session
        session.execute(text("INSERT INTO BOOKTABLE (ISBN, BOOKTABLE.NAME, PRESS, EDITOR, CATEGORY, ATTR1, ATTR2,"
                             "ATTR3, ATTR4, AMOUNT, BOOKTABLE.LEFT,PRICE ) "
                             "VALUES ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')"
                             .format(book.ISBN, book.name, book.press, book.editor, book.category, book.attr1,
                                     book.attr2,
                                     book.attr3, book.attr4, book.amount, book.left, book.price)))
        session.commit()
        session.close()
        return "书目添加成功！"



if __name__ == '__main__':
    print(BookService.Book_Query_All()[0].__dict__)
